<?php

namespace App\Http\Controllers\Admin\ExportReport;

use App\Admin\ContractGaiyao;
use Dompdf\Dompdf;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

class ExportReportHuLuDaoController extends Controller
{
    private $rowindex;

    public function export(Request $request)
    {
        $contract_id = $request->id; //批次 id模板
        //查询当前批次的信息
        $data_batch = DB::table('a_contract')->find($contract_id);
        $DY_RiQi = strtotime($data_batch->created_at); //将表示时间和日期的字符串转化为相应的时间戳
        //$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
        $report_num_pre = DB::table('setting')->where('id', '1')->value('report_num_pre'); //报告编号前缀
        $company_name = DB::table('setting')->where('id', '1')->value('name_rep'); //获取检测公司名称

        // https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#setting-the-print-header-and-footer-of-a-worksheet
        $template_file = './storage/ExcelTemplate/通用检测报告.xls'; //考虑到兼容Linux，斜杠用“/”
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取模板文件
        if (!file_exists($template_file)) {
            return '模板文件不存在';
        }
        //查询出当前批次下 标准、工器具的集合，并去重
        $data_std_tool = DB::table('a_contract_gaiyao as a')
                        ->leftJoin('a_contract as b', 'a.contract_id', '=', 'b.id')
                        ->leftJoin('a_standard as c', 'a.std_id', '=', 'c.id')
                        ->select('a.*', 'b.entrusted_company', 'b.client_name','b.batchNum','c.name as std_name')
                        ->where('a.contract_id', $contract_id)->get()->toArray();
        // $data_weather = DB::table('a_item_data as a')
        //                 ->leftJoin('a_contract_gaiyao as b','a.contract_gaiyao_id','=','b.id')
        //                 ->select('a.temp','a.hum','a.contract_gaiyao_id')
        //                 ->get();
        // $temp = $humi = [];
        // foreach ($data_weather as $item) {
        //     $temp[$item->contract_gaiyao_id][] = $item->temp;
        //     $humi[$item->contract_gaiyao_id][] = $item->hum;
        // }
        // dd($data_std_tool);
        $worksheetTemp = $spreadsheet->getActiveSheet(0); //在MS Excel（或其他适当的Spreadsheet程序）中打开工作簿时将处于活动状态的工作表
        foreach ($data_std_tool as $k => $v) {
            // dd($v,$temp);
            $v = (array)$v;
            // $minTem = min($temp[$v['id']]);
            // $maxTem = max($temp[$v['id']]);
            // $v['temp'] = $minTem == $maxTem ? $minTem : $minTem.'~'.$maxTem;
            // $minHum = min($humi[$v['id']]);
            // $maxHum = max($humi[$v['id']]);
            // $v['hum'] = $minHum == $maxHum ? $minHum : $minHum.'~'.$maxHum;
            //==========================去具体工器具模版文件 复制表单 到 封面模板文件========================
            $worksheet = clone $worksheetTemp; //按索引获取被复制的表单，从 0 开始
            $worksheet->setTitle("{$v['tool_name']}" . $k);
            $spreadsheet->addSheet($worksheet, $k + 1);
            //=========================================================================================
            //内容
            $this->content($worksheet, $v);
            unset($worksheet);
        } //最外层 foreach 结尾
        $spreadsheet->removeSheetByIndex(0);
        //----------直接在浏览器下载----start
        $file_name = "检测报告" . $data_batch->batchNum . ".xls"; //输出的文件名称
        // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出Excel 07文件
        header('Content-Type: application/vnd.ms-excel'); //告诉浏览器输出Excel 03版本文件
        header('Content-Disposition: attachment;filename=' . urlencode($file_name)); //告诉浏览器输出的文件名称
        header('Cache-Control: max-age=0'); //禁止缓存
        //----------直接在浏览器下载----end
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        $writer->setPreCalculateFormulas(false); //禁用公式预先计算
        // $writer->save('write.xls');//将Excel文件保存在服务器的磁盘上
        $writer->save('php://output'); //直接在浏览器下载
        //为了防止内存泄露，建议用完手动清理
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
    }

    //数据导入表中
    private function content($worksheet, $v)
    {
        // dd($v);
        $test_time = date('Y年m月d日',strtotime(DB::table('a_sample_info')->where('contract_gaiyao_id',$v['id'])->max('test_time')));
        $next_time = date('Y年m月d日',strtotime(DB::table('a_sample_info')->where('contract_gaiyao_id',$v['id'])->max('next_time')));
        //首行
        $worksheet->setCellValue('B4', $v['entrusted_company']);   //委托单位
        $worksheet->setCellValue('G4', $v['client_name']);   //送检单位，班组名称
        $worksheet->setCellValue('J4', $v['batchNum']);   //报告编号
        //一、试验参数
        $worksheet->setCellValue('C8', $test_time);   //试品名称
        $worksheet->setCellValue('I8', $next_time);   //电压等级
        // $worksheet->setCellValue('C10', $v['temp'] . '℃');   //试验温度
        // $worksheet->setCellValue('I10', $v['hum'] . '%RH');   //试验湿度
        $worksheet->setCellValue('C12', $v['test_time']);   //试验日期
        $worksheet->setCellValue('I12', $v['next_time']);   //下次试验日期
        $v['valid_date'] = DB::table('a_std_tool_rel')->where(['tool_id'=>$v['tool_id'],'std_id'=>$v['std_id']])->value('cycle') * 12;
        $worksheet->setCellValue('C14', $v['valid_date']);   //有效日期（月）
        //二、试验依据
        $worksheet->setCellValue('A18', $v['std_num'].'   '.$v['std_name']);
        //三、工具身份
        $this->bar_codes($worksheet, $v);
        //四、试验项目
        $this->items($worksheet, $v);
        //试验人员
        // if ((int)$v['manager_ids']) {
        //     $tester = DB::table('manager')->find($v['manager_ids']);
        //     $v['managers'] = $tester->realname.'  '.$tester->partner;
        // }
        // $worksheet->setCellValue('A' . ($this->rowindex + 3), $v['managers']);
        //盖章日期
        $worksheet->setCellValue('I35', $test_time);
    }

    //工具身份
    private function bar_codes($worksheet, $v)
    {
        $sample_info = DB::table('a_sample_info')->where('contract_gaiyao_id',$v['id'])->select('bar_code')->get();
        $this->rowindex = $baserowindex = 25;
        $bei4 = count($sample_info) / 4;
        // dd($bei4);
        if ($bei4 > 1) {
            $bei4 = floor($bei4);
            $worksheet->insertNewRowBefore($baserowindex, $bei4); //在第x行之前插入n个新行
            for ($i = 0; $i < $bei4; $i++) {
                $row = $baserowindex + $i;
                $worksheet->getRowDimension($row)->setRowHeight(25);
                $worksheet->mergeCells("A{$row}:B{$row}");
                $worksheet->mergeCells("C{$row}:F{$row}");
                $worksheet->mergeCells("G{$row}:H{$row}");
                $worksheet->mergeCells("I{$row}:K{$row}");
            }
        }
        foreach ($sample_info as $key => $item) {
            $yu = $key % 4;
            $bei = floor($key / 4);
            $row = $baserowindex + $bei;
            switch ($yu) {
                case 0:
                    $worksheet->setCellValue('A' . $row, $item->bar_code);
                    break;
                case 1:
                    $worksheet->setCellValue('C' . $row, $item->bar_code);
                    break;
                case 2:
                    $worksheet->setCellValue('G' . $row, $item->bar_code);
                    break;
                default:
                    $worksheet->setCellValue('I' . $row, $item->bar_code);
            }
        }
        $this->rowindex = $row;
    }

    //试验项目
    private function items($worksheet, $v)
    {
        $item_ids = explode(',', $v['item_ids']);
        $list = DB::table('a_std_tool_item_rel as a')
            ->leftJoin('a_condition_key as b', 'b.std_tool_item_rel_id', '=', 'a.id')
            ->leftJoin('a_condition as c', function ($join) use ($v) {
                $join->on('c.condition_key_id', '=', 'b.id')
                    ->whereIn('c.vol_class_id', [0, $v['vol_id'], $v['class_id']]);
            })
            ->leftJoin('a_voltage_class as d', 'c.vol_class_id', '=', 'd.id')
            ->leftJoin('a_testitem as e', 'e.id', '=', 'a.testitem_id')
            ->where(['a.std_id' => $v['std_id'], 'a.tool_id' => $v['tool_id']])
            ->whereIn('a.testitem_id', $item_ids)
            ->orderBy('a.sort','asc')
            ->select('a.jishu_yaoqiu','a.sort', 'b.key as condition_key', 'b.unit as condition_unit', 'c.val', 'd.val as vol_class_val', 'e.name as item_name')
            ->orderBy('a.sort','asc')
            ->distinct()->get();
        $ary = [];
        foreach ($list as $value) {
            if (isset($ary[$value->item_name])) {
                $ary[$value->item_name]['condition'] .= $this->getConditon($value);
            }
            else {
                $ary[$value->item_name] = [
                    'jishu_yaoqiu' => $value->jishu_yaoqiu ? $value->jishu_yaoqiu : '-',
                    'condition' => $this->getConditon($value),
                ];
            }
        }
        $item_names = explode('，', $v['item_names']);
        foreach ($item_names as $k => $item) {
            if (!isset($ary[$item])) {
                $ary[$item] = '良好';
            }
        }
        $baserowindex = $this->rowindex + 4;
        $count = count($ary) - 1;
        if ($count > 0) {
            $worksheet->insertNewRowBefore($baserowindex, $count); //在第x行之前插入n个新行
            for ($i = 0; $i < $count; $i++) {
                $row = $baserowindex + $i;
                $worksheet->mergeCells("B{$row}:C{$row}");
                $worksheet->mergeCells("D{$row}:F{$row}");
                $worksheet->mergeCells("G{$row}:K{$row}");
            }
        }
        $row = $this->rowindex + 4;
        $xu = 0;
        $row -= 1;
        foreach ($ary as $k => $v) {
            $row += 1;
            $xu += 1;
            $worksheet->setCellValue('A' . $row, $xu);   //序号
            $worksheet->setCellValue('B' . $row, $k);   //项目
            $worksheet->setCellValue('D' . $row, trim($v['condition'],PHP_EOL) ?: '/');   //试验条件
            $worksheet->setCellValue('G' . $row, $v['jishu_yaoqiu'] ?: '/');   //判断标准
            $conditionBei = substr_count($v['condition'], ':');
            $condition = explode(PHP_EOL,$v['condition']);
            foreach ($condition as $key => $value) {
                if (strlen($value) > 28) {
                    $conditionBei += 1;
                }
            }
            
            // dump($condition,$v['condition'],strlen($v['condition']),$conditionBei);
            if ($conditionBei > 0) {
                $worksheet->getRowDimension($row)->setRowHeight(16 * $conditionBei);
            }
            $len = strlen($v['jishu_yaoqiu']);
            if ($len > 65) {
                $bei = ceil($len / 65);
                if($conditionBei < $bei){
                    $worksheet->getRowDimension($row)->setRowHeight(16 * $bei);
                }
            }
        }
        // die;
        $this->rowindex = $row;
    }

    private function getConditon($value)
    {
        if ($value->condition_key) {
            $con = $value->condition_key . ':' . $value->val . $value->condition_unit . PHP_EOL;
            // if ($value->condition_key == '最小有效绝缘长度') {
                // code...
                // dd($con,strlen($con));
            // }
            return $con;
        }
        return '';
    }

}//类
